package com.gljx.web.controller.JournalController;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.gljx.util.ExcelExport;
import com.gljx.util.TimeFormat;
import com.gljx.web.Entity.PageBean;
import com.gljx.web.Entity.Student;
import com.gljx.web.Entity.User;
import com.gljx.web.service.StudentService;

@SuppressWarnings("deprecation")
@Controller
@RequestMapping("/")
public class StudentStateController {

	//自动注入
	@Autowired
	private StudentService studentService;

	//简写单元格创建
	public static HSSFCell setCell(HSSFCell cell, HSSFRow row, HSSFCellStyle cellStyleTitle, Object title, int num) {
		cell = row.createCell(num);
		cell.setCellStyle(cellStyleTitle);
		if (title == null || "".equals(title)) {
			cell.setCellValue(new HSSFRichTextString(""));
		} else {
			cell.setCellValue(new HSSFRichTextString(title.toString()));
		}
		return cell;
	}

	/**
	 * 重设单元格
	 *
	 * @author LiXiang  @date 2017年8月1日 下午4:53:40
	 */
	public static void setRegionStyle(HSSFSheet sheet, Region region, HSSFCellStyle cs) {
		for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
			HSSFRow row = HSSFCellUtil.getRow(i, sheet);
			for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
				HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
				cell.setCellStyle(cs);
			}
		}
	}

	/**
	 * 默认条件缺省查询学生考试明细表
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryStudentStateStatement/look.action")
	public String queryStudentStateStatement(HttpServletRequest request){
		PageBean<Student> pb = new PageBean<>();
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//获取本月第一天与最后一天
		Map<String, String> map = TimeFormat.thisMonth();
		//获取本月第一天
		String beginTime = map.get("beginTime");
		//获取本月第一天
		String endTime = map.get("endTime");
		try {
			//获取分页
			pb = getPageBean(request,user.getFcompanyid(),"","","",beginTime,endTime);
			//获取分页数据
			List<Student> students = pb.getBeanList();
			//把数据传到页面
			request.setAttribute("students", students);
			request.setAttribute("pb", pb);
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/journaling/financialJournaling/studentState/studentStateList";
	}

	/**
	 * 根据条件查询学生考试明细表
	 * @param request
	 * @param name 名称
	 * @param tel 电话
	 * @param cerno 身份证号
	 * @param beginTime 开始时间
	 * @param endTime 结束时间
	 * @return
	 */
	@RequestMapping("/queryStudentStateStatementByInfo")
	public String queryStudentStateStatementByInfo(HttpServletRequest request,String name,String tel,String cerno,String beginTime,String endTime){
		PageBean<Student> pb = new PageBean<>();
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		try {
			//获取分页
			pb = getPageBean(request,user.getFcompanyid(),name,tel,cerno,beginTime,endTime);
			//获取分页数据
			List<Student> students = pb.getBeanList();
			//把数据传到页面
			request.setAttribute("students", students);
			request.setAttribute("pb", pb);
			request.setAttribute("name", name);
			request.setAttribute("tel", tel);
			request.setAttribute("cerno", cerno);
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/journaling/financialJournaling/studentState/studentStateList";
	}
	
	/**
	 * 根据条件导出
	 * @param request
	 * @param psql2
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/exportStudentState")
	public String exportStudentState(HttpServletRequest request,HttpServletResponse response) throws Exception{
		String psql = request.getParameter("psql2");
		//查询学员信息
		List<Student> students = studentService.queryStuBySql(psql);
		String fileName = "驾校学员考试明细表.xls";
		fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
		response.reset();
		response.setHeader("Content-Disposition", "attachment;filename="
				+ fileName);// 指定下载的文件名
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Pragma", "no-cache");
		response.setHeader("Cache-Control", "no-cache");
		response.setDateHeader("Expires", 0);
		OutputStream output = response.getOutputStream();
		BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
		// 定义单元格报头
		String worksheetTitle = "驾校学员考试明细表";
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建单元格样式
		HSSFCellStyle cellStyleTitle = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTitle.setWrapText(true);
		//设置边框
		cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
		cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		 //设置背景颜色
		cellStyleTitle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        //填充背景颜色
		cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// ------------------------------------------------------------------
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// ------------------------------------------------------------------
		// 设置单元格字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyleTitle.setFont(font);
		//头部
		String fnumber = "编码";
		String fnameL2 = "名称";
		String cfrxsj = "入学时间";
		String cfidcardno = "身份证号";
		String cfsjhm = "手机";
		String cfsllx = "申领类型";
		String cfslzjcxdh = "申请准驾车型代号";
		String cfclazzName = "班级";
		String cfstudentstate = "学员状态";
		String tgsj = "通过时间";
		String kemuyi = "科目一";
		String kemuer = "科目二";
		String kemusan = "科目三";
		String wggsj = "未通过时间";
		String kscs = "考试次数";
		String qkcs = "缺考时间";
		String cfdesc = "备注";
		HSSFSheet sheet = wb.createSheet();
		ExcelExport exportExcel = new ExcelExport(wb, sheet);
		// 创建报表头部
		exportExcel.createNormalHead3(worksheetTitle, 21);
		//sheet.addMergedRegion(起始行，起始列，结束行，结束列)
		sheet.addMergedRegion(new Region(1, (short) 0, 2, (short) 0));
		sheet.addMergedRegion(new Region(1, (short) 1, 2, (short) 1));
		sheet.addMergedRegion(new Region(1, (short) 2, 2, (short) 2));
		sheet.addMergedRegion(new Region(1, (short) 3, 2, (short) 3));
		sheet.addMergedRegion(new Region(1, (short) 4, 2, (short) 4));
		sheet.addMergedRegion(new Region(1, (short) 5, 2, (short) 5));
		sheet.addMergedRegion(new Region(1, (short) 6, 2, (short) 6));
		sheet.addMergedRegion(new Region(1, (short) 7, 2, (short) 7));
		sheet.addMergedRegion(new Region(1, (short) 8, 2, (short) 8));
		sheet.addMergedRegion(new Region(1, (short) 9, 1, (short) 11));
		sheet.addMergedRegion(new Region(1, (short) 12, 1, (short) 14));
		sheet.addMergedRegion(new Region(1, (short) 15, 1, (short) 17));
		sheet.addMergedRegion(new Region(1, (short) 18, 1, (short) 20));
		sheet.addMergedRegion(new Region(1, (short) 21, 2, (short)21));
		// 定义第一行
		HSSFRow row1 = sheet.createRow(1);
		HSSFCell cell = row1.createCell(0);
		//定义第一行第1列
		cell = setCell(cell, row1, cellStyleTitle, fnumber, 0);
		//定义第一行第2列
		cell = setCell(cell, row1, cellStyleTitle, fnameL2, 1);
		//定义第一行第3列
		cell = setCell(cell, row1, cellStyleTitle, cfrxsj, 2);
		//定义第一行第4列
		cell = setCell(cell, row1, cellStyleTitle, cfidcardno, 3);
		//定义第一行第5列
		cell = setCell(cell, row1, cellStyleTitle, cfsjhm, 4);
		//定义第一行第6列
		cell = setCell(cell, row1, cellStyleTitle, cfsllx, 5);
		//定义第一行第7列
		cell = setCell(cell, row1, cellStyleTitle, cfslzjcxdh, 6);
		//定义第一行第8列
		cell = setCell(cell, row1, cellStyleTitle, cfclazzName, 7);
		//定义第一行第9列
		cell = setCell(cell, row1, cellStyleTitle, cfstudentstate, 8);
		//第一行第10列
		cell = setCell(cell, row1, cellStyleTitle, tgsj, 9);
		//第一行第11列
		cell = setCell(cell, row1, cellStyleTitle, wggsj, 12);
		//第一行第12列
		cell = setCell(cell, row1, cellStyleTitle, kscs, 15);
		//第一行第13列
		cell = setCell(cell, row1, cellStyleTitle, qkcs, 18);
		//第一行第14列
		cell = setCell(cell, row1, cellStyleTitle, cfdesc, 21);
		//创建第二行
		HSSFRow row2 = sheet.createRow(2);
		HSSFCell cell2 = row2.createCell(0);
		//第二行第10列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 0);
		//第二行第11列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 1);
		//第二行第12列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 2);
		//第二行第13列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 3);
		//第二行第14列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 4);
		//第二行第15列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 5);
		//第二行第16列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 6);
		//第二行第17列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 7);
		//第二行第18列
		cell2 = setCell(cell2, row2, cellStyleTitle, "", 8);
		//第二行第10列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemuyi, 9);
		//第二行第11列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemuer, 10);
		//第二行第12列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemusan, 11);
		//第二行第13列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemuyi, 12);
		//第二行第14列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemuer, 13);
		//第二行第15列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemusan, 14);
		//第二行第16列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemuyi, 15);
		//第二行第17列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemuer, 16);
		//第二行第18列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemusan, 17);
		//第二行第19列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemuyi, 18);
		//第二行第20列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemuer, 19);
		//第二行第21列
		cell2 = setCell(cell2, row2, cellStyleTitle, kemusan, 20);
		//设置高度
		row1.setHeightInPoints(30);
		row2.setHeightInPoints(30);
		//设置跨行跨列的边框
		setRegionStyle(sheet, new   Region(1,(short)9,1,(short)11),cellStyleTitle);
		setRegionStyle(sheet, new   Region(1,(short)12,1,(short)14),cellStyleTitle);
		setRegionStyle(sheet, new   Region(1,(short)15,1,(short)17),cellStyleTitle);
		setRegionStyle(sheet, new   Region(1,(short)18,1,(short)20),cellStyleTitle);
		setRegionStyle(sheet, new   Region(1,(short)21,2,(short)21),cellStyleTitle);
		//设置宽度
		sheet.setColumnWidth(0,fnumber.getBytes().length*3*256);
		sheet.setColumnWidth(2,cfrxsj.getBytes().length*2*150);
	    sheet.setColumnWidth(3,cfidcardno.getBytes().length*2*256);
	    sheet.setColumnWidth(4,cfsjhm.getBytes().length*2*256);
	    sheet.setColumnWidth(7,cfclazzName.getBytes().length*3*256);
	    sheet.setColumnWidth(8,cfstudentstate.getBytes().length*2*150);
	    sheet.setColumnWidth(9,kemuyi.getBytes().length*2*150);
	    sheet.setColumnWidth(10,kemuer.getBytes().length*2*150);
	    sheet.setColumnWidth(11,kemusan.getBytes().length*2*150);
	    sheet.setColumnWidth(12,kemuyi.getBytes().length*2*150);
	    sheet.setColumnWidth(13,kemuer.getBytes().length*2*150);
	    sheet.setColumnWidth(14,kemusan.getBytes().length* 2 * 150);
		//写入数据
		HSSFRow row = sheet.createRow(3);
		HSSFCell hssfCell = row.createCell(0);
		for (int i = 0; i < students.size(); i++) {
			Student student = new Student();
			//获取数据
			student = students.get(i);
			row = sheet.createRow(i + 3);
			//第i行第1列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getFnumber(), 0);
			//第i行第2列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getFnameL2(), 1);
			//第i行第3列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfrxsj(), 2);
			//第i行第4列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfidcardno(), 3);
			//第i行第5列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfsjhm(), 4);
			//第i行第6列
			hssfCell = row.createCell(5);
			hssfCell.setCellStyle(cellStyle);
			if(student.getCfsllx() == null){
				hssfCell.setCellValue(new HSSFRichTextString(""));
			}else if(student.getCfsllx() == 1){
				hssfCell.setCellValue(new HSSFRichTextString("初次申领"));
			}else if(student.getCfsllx() == 2){
				hssfCell.setCellValue(new HSSFRichTextString("增加准驾车型"));
			}else if(student.getCfsllx() == 3){
				hssfCell.setCellValue(new HSSFRichTextString("持军官驾驶证"));
			}else if(student.getCfsllx() == 4){
				hssfCell.setCellValue(new HSSFRichTextString("持境外驾驶证"));
			}else{
				hssfCell.setCellValue(new HSSFRichTextString(""));
			}
			//第i行第7列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfslzjcxdh(), 6);
			//第i行第8列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfclazzName(), 7);
			//第i行第9列
			hssfCell = row.createCell(8);
			hssfCell.setCellStyle(cellStyle);
			if(student.getCfstudentstate() == null){
				hssfCell.setCellValue(new HSSFRichTextString(""));
			}else if(student.getCfstudentstate() == 1){
				hssfCell.setCellValue(new HSSFRichTextString("已报名"));
			}else if(student.getCfstudentstate() == 2){
				hssfCell.setCellValue(new HSSFRichTextString("科目一通过"));
			}else if(student.getCfstudentstate() == 3){
				hssfCell.setCellValue(new HSSFRichTextString("科目一未通过"));
			}else if(student.getCfstudentstate() == 4){
				hssfCell.setCellValue(new HSSFRichTextString("科目二通过,科目三未开始"));
			}else if(student.getCfstudentstate() == 5){
				hssfCell.setCellValue(new HSSFRichTextString("科目二未通过,科目三未开始"));
			}else if(student.getCfstudentstate() == 6){
				hssfCell.setCellValue(new HSSFRichTextString("科目二练车中,科目三未开始"));
			}else if(student.getCfstudentstate() == 7){
				hssfCell.setCellValue(new HSSFRichTextString("科目三通过,科目二通过"));
			}else if(student.getCfstudentstate() == 8){
				hssfCell.setCellValue(new HSSFRichTextString("科目三未通过,科目二通过"));
			}else if(student.getCfstudentstate() == 9){
				hssfCell.setCellValue(new HSSFRichTextString("科目三练车中,科目二通过"));
			}else if(student.getCfstudentstate() == 10){
				hssfCell.setCellValue(new HSSFRichTextString("已结业"));
			}else if(student.getCfstudentstate() == 11){
				hssfCell.setCellValue(new HSSFRichTextString("已退学"));
			}else if(student.getCfstudentstate() == 12){
				hssfCell.setCellValue(new HSSFRichTextString("模拟考试合格"));
			}else if(student.getCfstudentstate() == 13){
				hssfCell.setCellValue(new HSSFRichTextString("已过期"));
			}else if(student.getCfstudentstate() == 14){
				hssfCell.setCellValue(new HSSFRichTextString("作废"));
			}else if(student.getCfstudentstate() == 15){
				hssfCell.setCellValue(new HSSFRichTextString("科目三练车中,科目二未通过"));
			}else if(student.getCfstudentstate() == 16){
				hssfCell.setCellValue(new HSSFRichTextString("科目三练车中,科目二未开始"));
			}else if(student.getCfstudentstate() == 17){
				hssfCell.setCellValue(new HSSFRichTextString("科目三通过,科目二未通过"));
			}else if(student.getCfstudentstate() == 18){
				hssfCell.setCellValue(new HSSFRichTextString("科目三通过,科目二练车中"));
			}else if(student.getCfstudentstate() == 19){
				hssfCell.setCellValue(new HSSFRichTextString("科目三通过,科目二未开始"));
			}else if(student.getCfstudentstate() == 20){
				hssfCell.setCellValue(new HSSFRichTextString("科目二未通过,科目三未通过"));
			} else if (student.getCfstudentstate() == 21) {
				hssfCell.setCellValue(new HSSFRichTextString("科目三未通过,科目二未开始"));
			} else {
				hssfCell.setCellValue(new HSSFRichTextString("科目二练车中,科目三未通过"));
			}
			//第i行第10列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmyherq(), 9);
			//第i行第11列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmehgrq(), 10);
			//第i行第12列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmshgrq(), 11);
			//第i行第13列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmybjgsj(), 12);
			//第i行第14列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmebjgsj(), 13);
			//第i行第15列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmsbjgsj(), 14);
			//第i行第16列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmykss(), 15);
			//第i行第17列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmekss(), 16);
			//第i行第18列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmskss(), 17);
			//第i行第19列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmyksqksj(), 18);
			//第i行第20列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmeksqksj(), 19);
			//第i行第21列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfkmsksqksj(), 20);
			//第i行第22列
			hssfCell = setCell(hssfCell, row, cellStyle, student.getCfdesc(), 21);
			//设置高度
			row.setHeightInPoints(30);
		}
		// 创建单元格样式
		HSSFCellStyle cellStyleTitle2 = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleTitle2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		// 指定单元格垂直居中对齐
		cellStyleTitle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//获取最后一行数
		int lastRow = students.size() + 3;
		sheet.addMergedRegion(new Region(lastRow, (short) 0, lastRow, (short) 2));
		sheet.addMergedRegion(new Region(lastRow, (short) 5, lastRow, (short) 7));
		sheet.addMergedRegion(new Region(lastRow, (short) 10, lastRow,(short)12));
		//设置最后一行
		row = sheet.createRow(lastRow);
		HSSFCell cellLast = row.createCell(0);
		//设置最后一行第1列
		cellLast = setCell(cellLast, row, cellStyleTitle2, "单位负责人:", 0);
		//设置最后一行第2列
		cellLast = setCell(cellLast, row, cellStyleTitle2, "财务经理:", 5);
		//设置最后一行第3列
		cellLast = setCell(cellLast, row, cellStyleTitle2, "制表人:", 10);
		//设置高度
		row.setHeightInPoints(30);
		try {
			bufferedOutPut.flush();
			wb.write(bufferedOutPut);
			bufferedOutPut.close();
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("Output   is   closed ");
		} finally {
			students.clear();
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类
	 * @throws Exception
	 */
	public PageBean<Student> getPageBean(HttpServletRequest request, String companyId,String name,String tel,String cerno, String beginTime, String endTime) throws Exception {

		int offset;//从第几条数据开始加载
		if (request.getParameter("pager.offset") == null) {
			offset = 0;
		} else {
			offset = Integer.parseInt(request.getParameter("pager.offset"));
		}
		StringBuffer psql = new StringBuffer();
		psql.append("select t.fid,to_char(t.CFRXSJ,'yyyy-MM-dd ')  cfrxsj,to_char(t.CFKMYHERQ,'YYYY-MM-DD')  cfkmyherq,to_char(t.CFKMEHGRQ,'YYYY-MM-DD') cfkmehgrq,");
		psql.append("to_char(t.CFKMSHGRQ,'YYYY-MM-DD') cfkmshgrq,to_char(t.CFKMYBJGSJ,'YYYY-MM-DD') cfkmybjgsj,to_char(t.CFKMSBJGSJ,'YYYY-MM-DD') cfkmsbjgsj,");
		psql.append("to_char(t.CFKMEBJGSJ,'YYYY-MM-DD') cfkmebjgsj,to_char(t.CFKMYKSQKSJ,'YYYY-MM-DD') cfkmyksqksj,to_char(t.CFKMSKSQKSJ,'YYYY-MM-DD') cfkmsksqksj,");
		psql.append("to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD') cfkmeksqksj,t.CFKMYKSS,t.CFKMEKSS,t.CFKMSKSS,t.fname_l2,t.fnumber,t.CFSJHM,t.CFIDCARDNO,t.CFSLLX,t.CFSLZJCXDH,t.cfstudentstate,");
		psql.append(" c.fname_l2 cfclazzName,t.cfdesc from ct_ds_student t left join ct_ds_clazz c on t.CFCLAZZID = c.fid where t.fcontrolunitid = '"+companyId+"'");
		if("".equals(name)){

		}else{
			psql.append(" and t.fname_l2 like '%"+name+"%'");
		}
		if("".equals(tel)){

		}else{
			psql.append(" and t.cfsjhm like '%"+tel+"%'");
		}
		if("".equals(cerno)){

		}else{
			psql.append(" and t.cfidcardno like '%"+cerno+"%'");
		}
		if("".equals(beginTime) || "".equals(endTime)){

		}else{
			psql.append("  and t.fcreatetime between to_date('"+beginTime+"','YYYY/MM/DD') and to_date('"+endTime+"','YYYY/MM/DD')");
		}
		request.setAttribute("psql", psql);
		int psize = 15; //每页显示数
		int pindex = offset / psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap = studentService.selectPage(pindex, psql.toString(), psize);
		PageBean<Student> pb = new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount = getPageBeanCount(psql.toString()); //取记录总数
		pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<Student>) paramMap.get("v_cur")); //每页数据集
		return pb;
	}

	/**
	 * 取记录总数 dmz 20160921
	 *
	 * @param psql SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception {

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap = studentService.selectPageCount(psql);
		return paramMap;
	}

}
